### Replication code for Taylor C. Boas, F. Daniel Hidalgo, and Guillermo Toral. "Competence versus Priorities: Negative Electoral Responses to Education Quality in Brazil"
### This file replicates the analyses in Appendix D.1 examining the relationship between education spending and IDEB
### R version, platform, and package versions reported at the end of the file
### June 1, 2020

# PREPARE THE ENVIRONMENT -------------------------------------------------
# Set Working Directory to wherever this file is located

# The directory where this file is located must also have a "figures" and a "tables" subdirectory
# Clean the environment
rm(list = ls())
# Load packages (make sure they are previously installed)
library(tidyverse); library(lmtest); library(texreg)

# IMPORT AND CLEAN DATA ---------------------------------------------------

# Municipal spending data, obtained from Brazil's Secretaria do Tesouro Nacional, "Contas Anuais", "Despesas por função": https://siconfi.tesouro.gov.br/siconfi/pages/public/consulta_finbra/finbra_list.jsf
finbra13 <- read.delim("data/finbra_despesas_2013.csv", sep=";", fileEncoding="latin1") # 2013 spending data
finbra17 <- read.delim("data/finbra_despesas_2017.csv", sep=";", fileEncoding="latin1") # 2017 spending data
s13 <- finbra13 %>%
  mutate(cod_conta_short = substr(conta,1,2),
         cod_conta_long = substr(conta,1,6)) %>%
  filter(coluna=="Despesas Pagas" & cod_conta_long=="12 - E") %>% # "Despesas pagas" = paid expenses, "12 - E" corresponds to education
  mutate(spending_education_2013 = valor) %>%
  dplyr::select(cod_ibge, spending_education_2013) 

finbra17$`valor.` <- gsub(",$", "", finbra17$`valor.`) # remove comma at the end of spending figures
s17 <- finbra17 %>%
  mutate(cod_conta_short = substr(conta,1,2),
         cod_conta_long = substr(conta,1,6)) %>%
  filter(coluna=="Despesas Pagas" & cod_conta_long=="12 - E") %>% # "Despesas pagas" = paid expenses, "12 - E" corresponds to education
  mutate(spending_education_2017 = as.numeric(`valor.`)) %>%
  dplyr::select(cod_ibge, spending_education_2017) 

s <- left_join(s17, s13) %>%
  mutate(delta_spending=spending_education_2017-spending_education_2013,
         delta_relative_spending=(spending_education_2017-spending_education_2013)/spending_education_2013)

# Municipal IDEB data at the primary schooling level ("ensino fundamental anos iniciais")
i <- read_csv("data/ideb_ef1_municipios.csv") %>%
  filter(rede=="Municipal") %>%
  mutate(ideb_gap_2013 = ideb_2013 - meta_2013,
         ideb_gap_2017 = ideb_2017 - meta_2017,
         delta_ideb_ef1 = ideb_2017 - ideb_2013,
         delta_rel_ideb_ef1 = delta_ideb_ef1 / ideb_2013,
         delta_ideb_gap_ef1 = ideb_gap_2017 - ideb_gap_2013,
         delta_rel_ideb_gap_ef1 = delta_ideb_gap_ef1 / ideb_gap_2013,
         ideb_2017_met = ifelse(ideb_gap_2017>=0,1,0)) %>%
  dplyr::select(cod_ibge, ideb_gap_2013, ideb_gap_2017, delta_ideb_ef1, delta_rel_ideb_ef1, delta_ideb_gap_ef1, delta_rel_ideb_gap_ef1, ideb_2017_met)

# Join spending and IDEB data
d <- left_join(s,i)

# TABLE D.30 --------------------------------------------------------------
# Regression models
m1 <- lm(ideb_2017_met ~ delta_relative_spending, data=d)
m2 <- lm(delta_ideb_ef1 ~ delta_relative_spending, data=d)
m3 <- lm(delta_ideb_gap_ef1 ~ delta_relative_spending, data=d)
m4 <- lm(delta_rel_ideb_ef1 ~ delta_relative_spending, data=d)
# Produce table in TEX format
texreg(list(m1,m2,m3,m4),
       file="tables/spending_ideb.tex",table=F,
       digits=4,
       override.se = list(coeftest(m1, type="HC2")[,2], coeftest(m2, type="HC2")[,2], coeftest(m3, type="HC2")[,2], coeftest(m4, type="HC2")[,2]),
       override.pvalues = list(coeftest(m1, type="HC2")[,4], coeftest(m2, type="HC2")[,4], coeftest(m3, type="HC2")[,4], coeftest(m4, type="HC2")[,4]),
       custom.header = list("Increase 2013 - 2017 in IDEB:" = 2:4),
       custom.model.names = c("2017 target met","Score", "Score - Target", "Score, rel. to 2013"),
       omit.coef="Intercept",
       custom.coef.names = "Education spending",
       custom.gof.names = c(NA, "Observations"),
       reorder.gof = c(2,1),
       include.adjrs = F,
       stars = c(0.01, 0.05, 0.1))

# FIGURE D.14 -------------------------------------------------------------
pdf("figures/spending_ideb.pdf",width=9,height=9)
plot(d$delta_relative_spending, d$delta_rel_ideb_ef1,
       xlim=c(-0.2,1),ylim=c(-0.2,0.5),cex=1.5,col=alpha("blue", 0.2),pch=16,
       xlab="Increase in education spending 2013-2017", ylab="Increase in municipal IDEB 2013-2017",cex.lab=1.5)
abline(lm(delta_rel_ideb_ef1 ~ delta_relative_spending, data=d),col="red",lwd=2)
dev.off()

# NOTES -- R version, platform, and loaded packages -------------------------
# sessionInfo(package = NULL)
# R version 3.6.3 (2020-02-29)
# Platform: x86_64-apple-darwin15.6.0 (64-bit)
# Running under: macOS Catalina 10.15.3
# 
# Matrix products: default
# BLAS:   /System/Library/Frameworks/Accelerate.framework/Versions/A/Frameworks/vecLib.framework/Versions/A/libBLAS.dylib
# LAPACK: /Library/Frameworks/R.framework/Versions/3.6/Resources/lib/libRlapack.dylib
# 
# locale:
#   [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
# 
# attached base packages:
#   [1] stats     graphics  grDevices utils     datasets  methods   base     
# 
# other attached packages:
#   [1] texreg_1.37.1   lmtest_0.9-37   zoo_1.8-7       forcats_0.5.0   stringr_1.4.0  
# [6] dplyr_0.8.5     purrr_0.3.3     readr_1.3.1     tidyr_1.0.2     tibble_3.0.0   
# [11] ggplot2_3.3.0   tidyverse_1.3.0
# 
# loaded via a namespace (and not attached):
#   [1] Rcpp_1.0.4       cellranger_1.1.0 pillar_1.4.3     compiler_3.6.3   dbplyr_1.4.2    
# [6] tools_3.6.3      lubridate_1.7.4  jsonlite_1.6.1   lifecycle_0.2.0  nlme_3.1-145    
# [11] gtable_0.3.0     lattice_0.20-40  pkgconfig_2.0.3  rlang_0.4.5      reprex_0.3.0    
# [16] cli_2.0.2        DBI_1.1.0        rstudioapi_0.11  yaml_2.2.1       haven_2.2.0     
# [21] withr_2.1.2      xml2_1.3.0       httr_1.4.1       fs_1.4.1         generics_0.0.2  
# [26] vctrs_0.2.4      hms_0.5.3        grid_3.6.3       tidyselect_1.0.0 glue_1.3.2      
# [31] R6_2.4.1         fansi_0.4.1      readxl_1.3.1     modelr_0.1.6     magrittr_1.5    
# [36] backports_1.1.5  scales_1.1.0     ellipsis_0.3.0   rvest_0.3.5      assertthat_0.2.1
# [41] colorspace_1.4-1 stringi_1.4.6    munsell_0.5.0    broom_0.5.5      crayon_1.3.4  